package com.demo.common.utils;

import com.demo.common.exceptions.ServiceException;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.CompletableFuture;

/**
 * db集群操作
 * TODO 可以优化的地方还很多，比如缓存，部分逻辑封装，后续有空再处理
 */
@Slf4j
public abstract class DbClusterUtils {

    /**
     * 批量查询counts
     * @param properyList
     * @param dataBaseRegular
     * @param selectSql
     * @return
     */
    @SneakyThrows
    public static List<DBResult<Long>> selectCounts(List<SimpleDataSourceProperty> properyList,String dataBaseRegular,String selectSql){
        return commonSelect(properyList,dataBaseRegular,selectSql,1,null);
    }

    /**
     * 批量查询单条记录
     * @param properyList
     * @param dataBaseRegular
     * @param selectSql
     * @param resultClass
     * @return
     */
    @SneakyThrows
    public static <T> List<DBResult<T>> selectOne(List<SimpleDataSourceProperty> properyList,String dataBaseRegular,String selectSql,Class<T> resultClass){
        return commonSelect(properyList,dataBaseRegular,selectSql,2,resultClass);
    }

    /**
     * 批量查询list
     * @param properyList
     * @param dataBaseRegular
     * @param selectSql
     * @return
     */
    @SneakyThrows
    public static List<DBResult<List>> selectList(List<SimpleDataSourceProperty> properyList,String dataBaseRegular,String selectSql){
        return commonSelect(properyList,dataBaseRegular,selectSql,3,null);
    }

    /**
     * 通用查询
     * @param properyList
     * @param dataBaseRegular
     * @param selectSql
     * @param selectType 1-查counts 2-单条明细 3-list
     * @param resultClass
     * @return
     * @param <T>
     */
    @SneakyThrows
    private static <T> List<DBResult<T>> commonSelect(List<SimpleDataSourceProperty> properyList,String dataBaseRegular,String selectSql,int selectType,Class<T> resultClass){
        List<DBResult<T>> resultList=new ArrayList<>();
        Object params;
        if(selectType==2){
            params=new BeanPropertyRowMapper<>(resultClass);
        } else {
            params = null;
        }
        List<CompletableFuture<List<DBResult<T>>>> futureList=new ArrayList<>(properyList.size());
        for (SimpleDataSourceProperty property : properyList) {
            CompletableFuture<List<DBResult<T>>> completableFuture = CompletableFuture.supplyAsync(() -> {
                List<DBResult<T>> singleResult = new ArrayList<>();
                DataSource dataSource = DataSourceBuilder.create().url(property.url).password(property.userPwd).driverClassName(property.driverClassName)
                        .driverClassName(property.driverClassName).username(property.userName).type(property.type).build();
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                if (property.moreDataBase) {
                    List<Map<String, Object>> maps = jdbcTemplate.queryForList("SHOW DATABASES;");
                    for (Map<String, Object> map : maps) {
                        String database = (String) map.get("DATABASE");
                        if (database.matches(dataBaseRegular)) {
                            jdbcTemplate.execute("use " + database);
                            Object data = doQuery(jdbcTemplate, selectSql, selectType, params);
                            DBResult result = new DBResult();
                            result.setDbName(database);
                            result.setResult(data);
                            singleResult.add(result);
                        }
                    }
                } else {
                    Object data = doQuery(jdbcTemplate, selectSql, selectType, params);
                    DBResult result = new DBResult();
                    result.setDbName(property.url);
                    result.setResult(data);
                    singleResult.add(result);
                }
                return singleResult;
            });
            futureList.add(completableFuture);
        }
        for (CompletableFuture<List<DBResult<T>>> listCompletableFuture : futureList) {
            resultList.addAll(listCompletableFuture.get());
        }
        return resultList;
    }

    /**
     * 实际查询
     * @param jdbcTemplate
     * @param querySql
     * @param selectType
     * @param param
     * @return
     */
    private static Object doQuery(JdbcTemplate jdbcTemplate,String querySql,int selectType,Object param){
        switch (selectType){
            case 1:
                return jdbcTemplate.queryForObject(querySql,Long.class);
            case 2:
                return jdbcTemplate.queryForObject(querySql,(RowMapper)param);
            case 3:
                return jdbcTemplate.queryForList(querySql);
            default:
                throw new ServiceException("查询类型异常");
        }
    }

    /**
     * 批量更新
     * @param properyList
     * @param dataBaseRegular
     * @param updateSql
     * @param ignoreDb
     * @return
     */
    @SneakyThrows
    public static List<DBResult<Integer>> update(List<SimpleDataSourceProperty> properyList, String dataBaseRegular, String updateSql, Set<String> ignoreDb){
        List<DBResult<Integer>> resultList=new ArrayList<>();
        List<CompletableFuture<List<DBResult<Integer>>>> futureList=new ArrayList<>();
        for (SimpleDataSourceProperty property : properyList) {
            CompletableFuture<List<DBResult<Integer>>> completableFuture = CompletableFuture.supplyAsync(() -> {
                List<DBResult<Integer>> singleResult = new ArrayList<>();
                DataSource dataSource = DataSourceBuilder.create().url(property.url).password(property.userPwd).driverClassName(property.driverClassName)
                        .driverClassName(property.driverClassName).username(property.userName).type(property.type).build();
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                if (property.moreDataBase) {
                    List<Map<String, Object>> maps = jdbcTemplate.queryForList("SHOW DATABASES;");
                    for (Map<String, Object> map : maps) {
                        String database = (String) map.get("DATABASE");
                        if (database.matches(dataBaseRegular) && !ignoreDb.contains(database)) {
                            jdbcTemplate.execute("use " + database);
                            int counts = jdbcTemplate.update(updateSql);
                            DBResult result = new DBResult();
                            result.setDbName(database);
                            result.setResult(counts);
                            singleResult.add(result);
                        }
                    }
                } else {
                    int counts = jdbcTemplate.update(updateSql);
                    DBResult result = new DBResult();
                    result.setDbName(property.url);
                    result.setResult(counts);
                    singleResult.add(result);
                }
                return singleResult;
            });
            futureList.add(completableFuture);
        }
        for (CompletableFuture<List<DBResult<Integer>>> listCompletableFuture : futureList) {
            resultList.addAll(listCompletableFuture.get());
        }
        return resultList;
    }

    /**
     * 批量更新匹配的数据库
     * @param properyList
     * @param updateSql
     * @param matchDb
     * @return
     */
    @SneakyThrows
    public static List<DBResult<Integer>> updateMatchDb(List<SimpleDataSourceProperty> properyList, String updateSql, Set<String> matchDb){
        List<DBResult<Integer>> resultList=new ArrayList<>();
        List<CompletableFuture<List<DBResult<Integer>>>> futureList=new ArrayList<>();
        for (SimpleDataSourceProperty property : properyList) {
            CompletableFuture<List<DBResult<Integer>>> completableFuture = CompletableFuture.supplyAsync(() -> {
                List<DBResult<Integer>> singleResult = new ArrayList<>();
                DataSource dataSource = DataSourceBuilder.create().url(property.url).password(property.userPwd).driverClassName(property.driverClassName)
                        .driverClassName(property.driverClassName).username(property.userName).type(property.type).build();
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                if (property.moreDataBase) {
                    List<Map<String, Object>> maps = jdbcTemplate.queryForList("SHOW DATABASES;");
                    for (Map<String, Object> map : maps) {
                        String database = (String) map.get("DATABASE");
                        if (matchDb.contains(database)) {
                            jdbcTemplate.execute("use " + database);
                            int counts = jdbcTemplate.update(updateSql);
                            DBResult result = new DBResult();
                            result.setDbName(database);
                            result.setResult(counts);
                            singleResult.add(result);
                        }
                    }
                } else {
                    int counts = jdbcTemplate.update(updateSql);
                    DBResult result = new DBResult();
                    result.setDbName(property.url);
                    result.setResult(counts);
                    singleResult.add(result);
                }
                return singleResult;
            });
            futureList.add(completableFuture);
        }
        for (CompletableFuture<List<DBResult<Integer>>> listCompletableFuture : futureList) {
            resultList.addAll(listCompletableFuture.get());
        }
        return resultList;
    }

    /**
     * 批量更新
     * @param properyList
     * @param dataBaseRegular
     * @param updateSqls
     * @param ignoreDb
     * @return
     */
    @SneakyThrows
    public static void batchUpdate(List<SimpleDataSourceProperty> properyList, String dataBaseRegular, List<String> updateSqls, Set<String> ignoreDb){
        for (SimpleDataSourceProperty property : properyList) {
            CompletableFuture.runAsync(()->{
                DataSource dataSource = DataSourceBuilder.create().url(property.url).password(property.userPwd).driverClassName(property.driverClassName)
                        .driverClassName(property.driverClassName).username(property.userName).type(property.type).build();
                JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
                if(property.moreDataBase){
                    List<Map<String, Object>> maps = jdbcTemplate.queryForList("SHOW DATABASES;");
                    for (Map<String, Object> map : maps) {
                        String database = (String) map.get("DATABASE");
                        if(database.matches(dataBaseRegular)&&!ignoreDb.contains(database)){
                            jdbcTemplate.execute("use "+database);
                            for (String updateSql : updateSqls) {
                                jdbcTemplate.update(updateSql);
                            }
                        }
                    }
                }else{
                    for (String updateSql : updateSqls) {
                        jdbcTemplate.update(updateSql);
                    }
                }
                log.info("dataSource {} update finish",property);
            });
        }
    }

    /**
     * 批量更新
     * @param property
     * @param updateSql 批量执行的sql语句
     * @return
     */
    @SneakyThrows
    public static List<DBSimpleUpdateResult> simpleUpdate(SimpleDataSourceProperty property,List<String> updateSql){
        List<DBSimpleUpdateResult> resultList=new ArrayList<>();
        DataSource dataSource = DataSourceBuilder.create().url(property.url).password(property.userPwd).driverClassName(property.driverClassName)
                .driverClassName(property.driverClassName).username(property.userName).type(property.type).build();
        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        for (String sql : updateSql) {
            DBSimpleUpdateResult result=new DBSimpleUpdateResult();
            result.setSql(sql);
            try {
                int counts = jdbcTemplate.update(sql);
                result.setUpdateCounts(counts);
            }catch (Exception e){
                log.error("execute sql:【{}】 match error",sql,e);
                result.setErrorMsg(e.getMessage());
            }
            resultList.add(result);
        }
        return resultList;
    }

    /**
     * 数据库更新执行结果(新增，修改，删除)
     */
    @Data
    public static class DBSimpleUpdateResult{
        private String sql;

        private int updateCounts=-1;

        private String errorMsg;
    }

    /**
     * 数据库执行结果
     */
    @Data
    public static class DBResult<T>{
        private String dbName="";

        private T result;
    }

    @Data
    public static class SimpleDataSourceProperty{

        public SimpleDataSourceProperty(String userName,String userPwd,String url){
            this.userName=userName;
            this.userPwd=userPwd;
            this.url=url;
        }

        private String userName;

        private String driverClassName="com.mysql.cj.jdbc.Driver";

        private String userPwd;

        private String url;

        private Class<? extends DataSource> type= HikariDataSource.class;
        /**
         * 一个账号多数据库，该项选是，会查询符合的数据库进行操作
         */
        private boolean moreDataBase=true;

    }
}
